{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DataDict SQL tmpl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fileName:fmt.txt\n"
     ]
    }
   ],
   "source": [
    "fileName = input(\"fileName:\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "modelName:tyzj\n"
     ]
    }
   ],
   "source": [
    "modelName = input(\"modelName:\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "class DictSqlTmpl:\n",
    "    def __init__(self):\n",
    "        self.delTmpl = \"\"\"DELETE FROM STTC_DATADICT WHERE CLSNO = '{0}';\n",
    "DELETE FROM STTC_DATALIST WHERE CLSNO = '{0}';\n",
    "\"\"\"\n",
    "        self.isrtListTmpl = \"\"\"\n",
    "INSERT INTO STTC_DATALIST (CLSNO, CLSNAME, FLAG, TBLNAME, MNTFLAG, LSTMNTDATE, LSTMNTUSER, EFFECTFLAG)\n",
    "VALUES ('{0}', '{1}', 'STTC_DATA', NULL, 'N', '2017-02-13', NULL, 'E');\n",
    "\"\"\"\n",
    "        self.isrtDictTmpl = \"\"\"\n",
    "INSERT INTO STTC_DATADICT (CLSNO, ITEMCODE, ORDERNO, CNNAME, ENNAME, REMARK, MNTFLAG, CREATEDATE, CREATEUSER, LSTMNTDATE, LSTMNTUSER, EFFECTFLAG, DEFAULTVALUE)\n",
    "VALUES ('{0}', '{1}', {2}, '{3}', NULL, NULL, 'Y', '2017-02-13', NULL, '2017-02-13', NULL, 'E', NULL);\n",
    "\"\"\"\n",
    "    def genTmpl(self,cnName,clsno,arr):\n",
    "        sqlTmpl = \"\\n\" +  \"--{0}:\".format(cnName) +  \",\".join(arr) + \"\\n\"\n",
    "        sqlTmpl += self.delTmpl.format(clsno)\n",
    "        sqlTmpl += self.isrtListTmpl.format(clsno,cnName)\n",
    "        orderno = 1\n",
    "        for itemname in arr:\n",
    "            itemcode = str(orderno)\n",
    "            sqlTmpl += self.isrtDictTmpl.format(clsno,itemcode,orderno,itemname)\n",
    "            orderno = orderno+1\n",
    "        return sqlTmpl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# generate datadict sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = open(fileName,\"r\",encoding='utf-8') #input file with format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "fsql = open(modelName + \".sql\",\"a+\",encoding='utf-8') #generate list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dictSqlTmpl = DictSqlTmpl()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "for line in f:\n",
    "    cnName,arrstr = line.split(':',1)\n",
    "    arr = arrstr.split()\n",
    "    cnName = cnName.strip()\n",
    "    enName = modelName.upper() + '_' + arr[0]\n",
    "    arr = arr[1:]\n",
    "    tmpl = dictSqlTmpl.genTmpl(cnName,enName,arr)\n",
    "    fsql.write(tmpl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "fsql.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
